package com.gzc.just.play.last.war.centralserver.repository;

import com.gzc.just.play.last.war.centralserver.user.model.User;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import jakarta.annotation.PostConstruct;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.Optional;
import java.util.List;

@Repository
@Profile("all-in-one")
public class JdbcStatusRepository implements StatusRepository {

    private final JdbcTemplate jdbcTemplate;

    public JdbcStatusRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @PostConstruct
    public void init() {
        // Create table if not exists (SQLite)
        String sql = """
            CREATE TABLE IF NOT EXISTS users (
                user_id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                nickname TEXT,
                level INTEGER NOT NULL DEFAULT 1,
                exp INTEGER NOT NULL DEFAULT 0,
                gold INTEGER NOT NULL DEFAULT 0,
                gems INTEGER NOT NULL DEFAULT 0,
                power INTEGER NOT NULL DEFAULT 100,
                max_power INTEGER NOT NULL DEFAULT 100,
                last_power_recover_time TEXT,
                guild_id INTEGER,
                main_character_id INTEGER,
                battle_power INTEGER DEFAULT 0,
                win_count INTEGER DEFAULT 0,
                lose_count INTEGER DEFAULT 0,
                rank_score INTEGER DEFAULT 0,
                rank_tier INTEGER DEFAULT 0,
                status TEXT NOT NULL,
                last_login_time TEXT,
                last_logout_time TEXT,
                create_time TEXT NOT NULL,
                update_time TEXT,
                is_vip BOOLEAN DEFAULT 0,
                vip_level INTEGER DEFAULT 0,
                vip_expire_time TEXT,
                remarks TEXT
            )
        """;
        jdbcTemplate.execute(sql);
    }

    @Override
    public User save(User user) {
        if (user.getUserId() == null) {
            return insert(user);
        } else {
            update(user);
            return user;
        }
    }

    private User insert(User user) {
        String sql = """
            INSERT INTO users (username, password_hash, nickname, level, exp, gold, gems, power, max_power, 
            last_power_recover_time, guild_id, main_character_id, battle_power, win_count, lose_count, 
            rank_score, rank_tier, status, last_login_time, last_logout_time, create_time, update_time, 
            is_vip, vip_level, vip_expire_time, remarks) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """;

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            setStatementParams(ps, user);
            return ps;
        }, keyHolder);

        Number key = keyHolder.getKey();
        if (key != null) {
            user.setUserId(key.longValue());
        }
        return user;
    }

    private void update(User user) {
        String sql = """
            UPDATE users SET username=?, password_hash=?, nickname=?, level=?, exp=?, gold=?, gems=?, power=?, max_power=?, 
            last_power_recover_time=?, guild_id=?, main_character_id=?, battle_power=?, win_count=?, lose_count=?, 
            rank_score=?, rank_tier=?, status=?, last_login_time=?, last_logout_time=?, create_time=?, update_time=?, 
            is_vip=?, vip_level=?, vip_expire_time=?, remarks=? 
            WHERE user_id=?
        """;
        
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql);
            setStatementParams(ps, user);
            ps.setLong(27, user.getUserId());
            return ps;
        });
    }

    private void setStatementParams(PreparedStatement ps, User user) throws SQLException {
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getPasswordHash());
        ps.setString(3, user.getNickname());
        ps.setInt(4, user.getLevel());
        ps.setLong(5, user.getExp());
        ps.setLong(6, user.getGold());
        ps.setLong(7, user.getGems());
        ps.setInt(8, user.getPower());
        ps.setLong(9, user.getMaxPower());
        ps.setString(10, user.getLastPowerRecoverTime() != null ? user.getLastPowerRecoverTime().toString() : null);
        setLongOrNull(ps, 11, user.getGuildId());
        setLongOrNull(ps, 12, user.getMainCharacterId());
        ps.setInt(13, user.getBattlePower());
        ps.setInt(14, user.getWinCount());
        ps.setInt(15, user.getLoseCount());
        ps.setInt(16, user.getRankScore());
        ps.setInt(17, user.getRankTier());
        ps.setString(18, user.getStatus().name());
        ps.setString(19, user.getLastLoginTime() != null ? user.getLastLoginTime().toString() : null);
        ps.setString(20, user.getLastLogoutTime() != null ? user.getLastLogoutTime().toString() : null);
        ps.setString(21, user.getCreateTime() != null ? user.getCreateTime().toString() : LocalDateTime.now().toString());
        ps.setString(22, user.getUpdateTime() != null ? user.getUpdateTime().toString() : LocalDateTime.now().toString());
        ps.setBoolean(23, user.getIsVip());
        ps.setInt(24, user.getVipLevel());
        ps.setString(25, user.getVipExpireTime() != null ? user.getVipExpireTime().toString() : null);
        ps.setString(26, user.getRemarks());
    }

    private void setLongOrNull(PreparedStatement ps, int index, Long value) throws SQLException {
        if (value != null) {
            ps.setLong(index, value);
        } else {
            ps.setNull(index, java.sql.Types.INTEGER);
        }
    }

    @Override
    public Optional<User> findById(Long id) {
        String sql = "SELECT * FROM users WHERE user_id = ?";
        List<User> users = jdbcTemplate.query(sql, new UserRowMapper(), id);
        return users.isEmpty() ? Optional.empty() : Optional.of(users.get(0));
    }

    @Override
    public Optional<User> findByUsername(String username) {
        String sql = "SELECT * FROM users WHERE username = ?";
        List<User> users = jdbcTemplate.query(sql, new UserRowMapper(), username);
        return users.isEmpty() ? Optional.empty() : Optional.of(users.get(0));
    }

    @Override
    public void deleteById(Long id) {
        String sql = "DELETE FROM users WHERE user_id = ?";
        jdbcTemplate.update(sql, id);
    }

    @Override
    public boolean existsById(Long id) {
        String sql = "SELECT count(*) FROM users WHERE user_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return count != null && count > 0;
    }

    private static class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setUserId(rs.getLong("user_id"));
            user.setUsername(rs.getString("username"));
            user.setPasswordHash(rs.getString("password_hash"));
            user.setNickname(rs.getString("nickname"));
            user.setLevel(rs.getInt("level"));
            user.setExp(rs.getLong("exp"));
            user.setGold(rs.getLong("gold"));
            user.setGems(rs.getLong("gems"));
            user.setPower(rs.getInt("power"));
            user.setMaxPower(rs.getLong("max_power"));
            
            String lastPowerTime = rs.getString("last_power_recover_time");
            if (lastPowerTime != null) user.setLastPowerRecoverTime(LocalDateTime.parse(lastPowerTime));
            
            long guildId = rs.getLong("guild_id");
            if (!rs.wasNull()) user.setGuildId(guildId);
            
            long mainCharId = rs.getLong("main_character_id");
            if (!rs.wasNull()) user.setMainCharacterId(mainCharId);
            
            user.setBattlePower(rs.getInt("battle_power"));
            user.setWinCount(rs.getInt("win_count"));
            user.setLoseCount(rs.getInt("lose_count"));
            user.setRankScore(rs.getInt("rank_score"));
            user.setRankTier(rs.getInt("rank_tier"));
            user.setStatus(User.UserStatus.valueOf(rs.getString("status")));
            
            String lastLogin = rs.getString("last_login_time");
            if (lastLogin != null) user.setLastLoginTime(LocalDateTime.parse(lastLogin));
            
            String lastLogout = rs.getString("last_logout_time");
            if (lastLogout != null) user.setLastLogoutTime(LocalDateTime.parse(lastLogout));
            
            String createTime = rs.getString("create_time");
            if (createTime != null) user.setCreateTime(LocalDateTime.parse(createTime));
            
            String updateTime = rs.getString("update_time");
            if (updateTime != null) user.setUpdateTime(LocalDateTime.parse(updateTime));
            
            user.setIsVip(rs.getBoolean("is_vip"));
            user.setVipLevel(rs.getInt("vip_level"));
            
            String vipExpire = rs.getString("vip_expire_time");
            if (vipExpire != null) user.setVipExpireTime(LocalDateTime.parse(vipExpire));
            
            user.setRemarks(rs.getString("remarks"));
            
            return user;
        }
    }
}
